-- 用户基本信息表
CREATE TABLE users (
    id SERIAL NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL DEFAULT '',           -- 用户名称
    password CHAR(32) NOT NULL DEFAULT '',          -- 用户密码
    secret CHAR(32) NOT NULL DEFAULT '',            -- 安全密码
    status SMALLINT NOT NULL DEFAULT 1,             -- 状态, 0: 禁用; 1: 正常; 2: 锁定;
    last_login TIMESTAMPTZ NOT NULL DEFAULT NOW(),  -- 最后登录时间
    login_count INT NOT NULL DEFAULT 0,             -- 登录次数
    last_ip INET,                                   -- 最后登录IP
    created TIMESTAMPTZ NOT NULL DEFAULT NOW(),     -- 注册时间
    updated TIMESTAMPTZ NOT NULL DEFAULT NOW()      -- 最后修改时间
) PARTITION BY HASH(id);

-- 创建索引
CREATE INDEX ON users(name);
CREATE INDEX ON users(status);

-- 创建分区
CREATE TABLE users_01 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 1);
CREATE TABLE users_02 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 2);
CREATE TABLE users_03 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 3);
CREATE TABLE users_04 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 4);
CREATE TABLE users_05 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 5);
CREATE TABLE users_06 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 6);
CREATE TABLE users_07 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 7);
CREATE TABLE users_08 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 8);
CREATE TABLE users_09 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 9);
CREATE TABLE users_10 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 10);
CREATE TABLE users_11 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 11);
CREATE TABLE users_12 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 12);
CREATE TABLE users_13 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 13);
CREATE TABLE users_14 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 14);
CREATE TABLE users_15 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 15);
CREATE TABLE users_16 PARTITION OF users (id) FOR VALUES WITH (MODULUS 16, REMAINDER 0);

-- 触发器
-- 新用户注册时,初始化其账户信息、基本资料
CREATE OR REPLACE FUNCTION user_init_profile()
    RETURNS TRIGGER
AS $$
    BEGIN
        -- 生成用户账户信息
        INSERT INTO user_accounts(user_id, user_name) VALUES (NEW.id, NEW.name);
        -- 生成用户基本信息
        INSERT INTO user_details(user_id, user_name) VALUES (NEW.id, NEW.name);
        RETURN NULL;
    END;
$$ LANGUAGE plpgsql;
DROP TRIGGER user_init ON users;
CREATE TRIGGER user_init
    AFTER INSERT ON users
    FOR EACH ROW EXECUTE PROCEDURE user_init_profile();

-- 存储过程
-- 用户登录判断
CREATE OR REPLACE FUNCTION user_login(p_name VARCHAR, p_password VARCHAR, v_ip VARCHAR)
    RETURNS TEXT
AS $$
    DECLARE
        r users%ROWTYPE;
        v_real_password VARCHAR;
        v_secret VARCHAR;
        v_new_password VARCHAR;
    BEGIN
        SELECT * INTO r FROM users WHERE name = p_name LIMIT 1;
        IF r IS NULL THEN  -- 如果没有找到到记录
            RETURN '用户名称或者登录密码有误';
        ELSE
            v_real_password = encrypt_password(p_password, r.secret); -- 真正的密码
            IF v_real_password <> r.password THEN -- 如果密码不正确
                RETURN '用户名称或者用户密码错误';
            ELSEIF r.status = 0 THEN
                RETURN '用户账号已被停用';
            END iF;
        END IF;

        v_secret = secret_string();
        v_new_password = encrypt_password(p_password, v_secret);
        UPDATE users
            SET
                secret = v_secret,              -- 新的密盐
                password = v_new_password,      -- 密码
                login_count = login_count + 1,  -- 登录次数
                last_login = NOW(),             -- 上次登录时间
                last_ip = v_ip::inet,           -- 上次登录ip
                updated = NOW()                 -- 更新时间
            WHERE id = r.id;
        RETURN 'ok';
    END;
$$ LANGUAGE plpgsql;

-- 生成初始密码
CREATE OR REPLACE FUNCTION user_login_init()
    RETURNS TABLE(secret VARCHAR, password VARCHAR)
AS $$
    DECLARE
        v_secret VARCHAR;
        v_password VARCHAR;
    BEGIN
        v_secret = secret_string();
        v_password = password_with_secret('qwe123', v_secret);
        UPDATE users
            SET
                secret = v_secret,
                password = v_password
            WHERE name = 'temp_name1';
        RETURN QUERY SELECT v_secret, v_password;
    END;
$$ LANGUAGE plpgsql;
